import pymysql

def filterParam(param): # 过滤参数防止sql注入
    res = param
    keywordMap = [';', '\'', '&', '#', '%', '<', '>', '=', '-', 'or', 'and']
    for key in keywordMap:
        res = res.replace(key, 'X')
    return res
class MYSQL():
    def runSql(self, statement):
        conn = pymysql.connect(host="127.0.0.1",user="root",password="root",database="chess",charset="utf8")
        cursor = conn.cursor()
        ret = None
        try:
            cursor.execute(statement)
            ret = cursor.fetchall()
            conn.commit()
        except Exception as e:
            print('-------sql execute error-------')
            print(statement)
            conn.rollback()
        cursor.close()
        conn.close()
        return ret

    def login(self, name, pwd):
        n = filterParam(name)
        p = filterParam(pwd)
        res = self.runSql('select * from `user` where name =\''+n+'\' and pass = \''+p+'\';')
        if len(res) > 0:
            d = { 'id': res[0][0], 'session': res[0][3], 'win': res[0][4], 'escape': res[0][5],
                  'email': res[0][6], 'level': res[0][7], 'url': res[0][8] }
            return d
        return None

    def register(self, name, pwd, email):
        n = filterParam(name)
        p = filterParam(pwd)
        e = filterParam(email)
        test = self.runSql('select name from `user` where name =\''+n+'\'')
        if test != (): # 需要保证用户名唯一
            return False
        self.runSql('INSERT INTO `user` SET name = \''+n+'\', pass = \''+p+'\', email = \''+e+'\';')
        res = self.runSql('select * from `user` where name =\''+n+'\' and pass = \''+p+'\';')
        if len(res) > 0:
            return True
        return False

    def uploadHead(self, uid, url):
        self.runSql('UPDATE `user` SET headUrl = \'' + url + '\' WHERE id =' + str(uid))
        return {'succeed': True}

    def getUserInfo(self, roomid):
        res = self.runSql('SELECT `name`, session, win, escape, `level`, headUrl\
            FROM `user` WHERE `user`.id IN (SELECT playera FROM hall WHERE roomid = '+str(roomid)+')')
        pa = None
        if res is not ():
            pa = { 'name': res[0][0], 'session': res[0][1], 'rate': res[0][2],
              'escape': res[0][3], 'level': res[0][4], 'headUrl': res[0][5] }
        res = self.runSql('SELECT `name`, `level`, win, escape, session, headUrl\
            FROM `user` WHERE `user`.id IN (SELECT playerb FROM hall WHERE roomid = '+str(roomid)+')')
        pb = None
        if res is not ():
            pb = { 'name': res[0][0], 'session': res[0][1], 'rate': res[0][2],
              'escape': res[0][3], 'level': res[0][4], 'headUrl': res[0][5] }
        return { 'a': pa, 'b': pb }

    def setUserInfo(self, userData):
        self.runSql('UPDATE `user` SET session = '+str(userData['pa']['session'])+', level = \''+
                    userData['pa']['level']+ '\', escape = '+str(userData['pa']['escape'])+', win = '+
                    str(userData['pb']['win'])+' WHERE name = \''+userData['pa']['name']+'\'')
        self.runSql('UPDATE `user` SET session = '+str(userData['pb']['session'])+', level = \''+
                    userData['pb']['level']+ '\', escape = '+str(userData['pb']['escape'])+', win = '+
                    str(userData['pb']['win'])+' WHERE name = \''+userData['pb']['name']+'\'')

    def getAllGame(self): #需要将结果处理成字典
        res = self.runSql('''
            SELECT roomid, t.name, t.level, t.win, u.name, u.level, u.win, t.pwd, t.remarks, t.state
            FROM ( SELECT roomid, `name`, `level`, win, pwd, playerb, remarks, state
            FROM `user` RIGHT JOIN hall ON `user`.id = hall.playera
            ) AS t LEFT JOIN `user` AS u ON t.playerb = u.id ''')
        roomList = []
        for item in res:
            temp = { 'roomId': item[0], 'pass': item[7], 'remarks': item[8], 'state': item[9] }
            if item[1]:
                temp['a'] = { 'name': item[1], 'level': item[2], 'win': item[3] }
            if item[4]:
                temp['b'] = { 'name': item[4], 'level': item[5], 'win': item[6] }
            roomList.append(temp)
        return roomList

    def create(self, playerAId, playerBId, rule, pwd, remarks):
        allRoomId = self.runSql('select roomId from hall;')
        resRoomId = 0
        succeed = None
        if len(allRoomId) > 0:
            roomList = []
            for item in allRoomId:
                roomList.append(item[0])
            roomList.sort()
            resRoomId = roomList[0] - 1
            for item in roomList:
                if item == resRoomId + 1:
                    resRoomId += 1
                    continue
                else:
                    break
            resRoomId += 1
        else:
            resRoomId = 1
        player = ', playera = ' + str(playerAId) if (playerBId or '') == '' else ', playerb = ' + str(playerBId)
        self.runSql('INSERT INTO `hall` SET roomid = '+str(resRoomId)+player+', rule = \''+rule+'\', remarks = \''+remarks+'\', pwd = \''+pwd+'\';')
        succeed = self.runSql('select * from hall where roomid = ' + str(resRoomId) + ';')
        print(succeed, len(succeed))
        if succeed is not None:
            return resRoomId
        else:
            return None

    def enter(self, playerId, roomId):
        succeed = []
        allData = self.runSql('SELECT playera, playerb, rule from hall WHERE roomid =' + str(roomId))
        if allData == ():
            return None
        playerList = [allData[0][0], allData[0][1]]
        if playerList[0] and playerList[1]: # 优化：观战模式
            return None
        if playerList[0]:
            playerList[1] = playerId
        else:
            playerList[0] = playerId
        playerList[0] = playerList[0] or '(NULL)'
        playerList[1] = playerList[1] or '(NULL)'
        succeed = self.runSql('UPDATE `hall` SET playera = '+str(playerList[0])+', playerb='+str(playerList[1])+' WHERE roomid = '+str(roomId)+';')
        if succeed == ():
            return { 'pa': int(playerList[0]), 'pb': int(playerList[1]), 'rule': allData[0][2] }
        else:
            return None

    def leave(self, playerId, roomId):
        allData = self.runSql('SELECT playera, playerb from hall WHERE roomid =' + str(roomId))
        playerList = [None, None]
        if allData is not ():
            playerList = [allData[0][0], allData[0][1]]
        removeRoom = True
        if playerList[0] == playerId:
            if playerList[1] is None:
                removeRoom = False
                self.runSql('DELETE FROM `hall` WHERE roomid = '+str(roomId)+';')
            else:
                removeRoom = True
                self.runSql('UPDATE `hall` SET playera = (NULL) WHERE roomid = '+str(roomId)+';')
        elif playerList[1] == playerId:
            if playerList[0] is None:
                removeRoom = False
                self.runSql('DELETE FROM `hall` WHERE roomid = '+str(roomId)+';')
            else:
                removeRoom = True
                self.runSql('UPDATE `hall` SET playerb = (NULL) WHERE roomid = '+str(roomId)+';')
        else:
            self.runSql('DELETE FROM `hall` WHERE roomid = '+str(roomId)+';')
        return removeRoom
